﻿using System;
using System.Net.Sockets;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;
using Tamir.SharpSsh.jsch;

namespace aDream
{
    public class DataConnection
    {
        private Session session;

        public DataConnection()
        {
            session = ssh();
        }

       public DataTable ExecuteReturningQuery(String sSQL)
        {
            String connectionString = "Server=scarecrowserver.dyndns-free.com;Port=3306;Database=adream2;Uid=simran;Pwd=default";
            MySqlConnection connection = new MySqlConnection(connectionString);
            connection.Open();
            MySqlCommand command = new MySqlCommand();
            command.CommandText = sSQL;
            command.Connection = connection;
            command.CommandType = CommandType.Text;

            MySqlDataAdapter da = new MySqlDataAdapter(command);
            DataTable dt = new DataTable();
            da.Fill(dt);

            connection.Close();
            return dt;
        }

        public int ExecuteNonReturningQuery(String sSQL)
        {
            MySqlConnection connnection;
            MySqlCommand command;
            int affectedRows;
            String str;

            String connectionString = "Server=scarecrowserver.dyndns-free.com;Port=3306;Database=adream2;Uid=simran;Pwd=default";
            connnection = new MySqlConnection(connectionString);

            command = new MySqlCommand();
            command.CommandText = sSQL;
            command.Connection = connnection;
            command.CommandType = CommandType.Text;

            try
            {
                connnection.Open();
                affectedRows = command.ExecuteNonQuery();
            }

            catch (Exception exc)
            {   affectedRows = -1;
                str = exc.Message;
                throw new Exception("MySQL : " + str);
            }
            connnection.Close();
            return affectedRows;
        }
     private Session ssh()
        {
            Session session;
            try
            {
                Hashtable config = new Hashtable();
                config.Add("StrictHostKeyChecking", "No");
                JSch connection = new JSch();
                string host = "scarecrowserver.dyndns-free.com";
                string user = "simran";
                string password = "dimsim";
                int sshPort = 22;
                session = connection.getSession(user, host, sshPort);
                session.setConfig(config);
                session.setHost(host);
                session.setPassword(password);
                session.connect();
                session.setPortForwardingL(3306, "localhost", 3306);
                return session;
            }
            catch
            {
                return null;
            }
        }
    }
}
